using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using MM.Entities;

namespace MM.Business
{
    public class clsPHIEU_CHUYEN_KHO
    {
        public clsPHIEU_CHUYEN_KHO(string sConn)
        {
            SqlHelper.ConnectionString = sConn;
        }

        #region Private methods
        private PHIEU_CHUYEN_KHO GetDataRow(DataRow row)
        {
            PHIEU_CHUYEN_KHO objPHIEU_CHUYEN_KHO = new PHIEU_CHUYEN_KHO();
            try
            {
                if (row["MaPhieuChuyenKho"] != DBNull.Value)
                    objPHIEU_CHUYEN_KHO.MaPhieuChuyenKho = row["MaPhieuChuyenKho"].ToString();
            }
            catch { }
            try
            {
                if (row["NgayXuat"] != DBNull.Value)
                    objPHIEU_CHUYEN_KHO.NgayXuat = Convert.ToDateTime((row["NgayXuat"]));
            }
            catch { }
            try
            {
                if (row["NgayExport"] != DBNull.Value)
                    objPHIEU_CHUYEN_KHO.NgayExport = Convert.ToDateTime((row["NgayExport"]));
            }
            catch { }
            try
            {
                if (row["NguoiXuat"] != DBNull.Value)
                    objPHIEU_CHUYEN_KHO.NguoiXuat = (row["NguoiXuat"].ToString());
            }
            catch { }
            try
            {
                if (row["NguoiExport"] != DBNull.Value)
                    objPHIEU_CHUYEN_KHO.NguoiExport = (row["NguoiExport"].ToString());
            }
            catch { }          
            try
            {
                if (row["TinhTrang"] != DBNull.Value)
                    objPHIEU_CHUYEN_KHO.TinhTrang = int.Parse(row["TinhTrang"].ToString());
            }
            catch { }
            try
            {
                if (row["SoLanExport"] != DBNull.Value)
                    objPHIEU_CHUYEN_KHO.SoLanExport = int.Parse(row["SoLanExport"].ToString());
            }
            catch { }
            try
            {
                if (row["PlantReferenceSAP"] != DBNull.Value)
                    objPHIEU_CHUYEN_KHO.PlantReferenceSAP = (row["PlantReferenceSAP"].ToString());
            }
            catch { }            
            try
            {
                if (row["GhiChu"] != DBNull.Value)
                    objPHIEU_CHUYEN_KHO.GhiChu = (row["GhiChu"].ToString());
            }
            catch { }
            

            return objPHIEU_CHUYEN_KHO;
        }
        private List<PHIEU_CHUYEN_KHO> GetPHIEU_CHUYEN_KHO(DataTable dt)
        {
            List<PHIEU_CHUYEN_KHO> lst = new List<PHIEU_CHUYEN_KHO>();
            PHIEU_CHUYEN_KHO obj;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                obj = this.GetDataRow(dt.Rows[i]);
                lst.Add(obj);
            }
            return lst;
        }
        #endregion Private Method

        public string InsertPHIEU_CHUYEN_KHO(PHIEU_CHUYEN_KHO obj, List<CHI_TIET_PCK> lstCT, string matau)
        {
            string spName = "";
            SqlParameter[] par;
            int iResult = 0;
            string maphieuchuyenkho = "";
            try
            {
                SqlHelper.BeginTran();
                spName = "usp_InsertPHIEU_CHUYEN_KHO";
                par = new SqlParameter[9];
                par[0] = new SqlParameter("@MaPhieuChuyenKho", SqlDbType.VarChar, 10);
                par[0].Direction = ParameterDirection.Output;
                par[1] = new SqlParameter("@NgayXuat", obj.NgayXuat);
                par[2] = new SqlParameter("@NguoiXuat", obj.NguoiXuat);
                par[3] = new SqlParameter("@TinhTrang", obj.TinhTrang);
                par[4] = new SqlParameter("@SoLanExport", obj.SoLanExport);              
                par[5] = new SqlParameter("@PlantReferenceSAP", obj.PlantReferenceSAP);
                par[6] = new SqlParameter("@NgayExport", null);
                par[7] = new SqlParameter("@NguoiExport", obj.NguoiExport);
                par[8] = new SqlParameter("@MaTau", matau);
                

                iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);

                maphieuchuyenkho = par[0].Value.ToString();
                if (maphieuchuyenkho.Length != 10)
                {
                    SqlHelper.Tran.Rollback();
                    iResult = -1;
                }
                else
                {
                    int i = 0;

                    for (i = 0; i < lstCT.Count; i++)
                    {

                        spName = "usp_InsertCHI_TIET_PCK";
                        par = new SqlParameter[11];
                        par[0] = new SqlParameter("@MaChiTiet", lstCT[i].MaChiTiet);
                        par[1] = new SqlParameter("@SoLuong", lstCT[i].SoLuong);
                        par[2] = new SqlParameter("@MaThietBi", lstCT[i].MaThietBi);
                        par[3] = new SqlParameter("@TinhTrang", lstCT[i].TinhTrang);
                        par[4] = new SqlParameter("@MaPhieuChuyenKho", maphieuchuyenkho);
                        par[5] = new SqlParameter("@MaPhieuHuyChuyenKho", lstCT[i].MaPhieuHuyChuyenKho);
                        par[6] = new SqlParameter("@MaKhoNguon", lstCT[i].MaKhoNguon);
                        par[7] = new SqlParameter("@MaKhoDich", lstCT[i].MaKhoDich);
                        par[8] = new SqlParameter("@MaTrangThai", lstCT[i].MaTrangThai);
                        par[9] = new SqlParameter("@NguoiTao", obj.NguoiXuat);
                        par[10] = new SqlParameter("@NgayTao", obj.NgayXuat);                        


                        iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);

                        if (iResult <= 0)
                        {
                            SqlHelper.Tran.Rollback();
                            maphieuchuyenkho = "";
                            break;
                        }


                    }
                    if (i == lstCT.Count && i > 0)
                    {
                        SqlHelper.CommitTran();
                    }


                }



            }
            catch 
            {
                SqlHelper.Tran.Rollback();
                maphieuchuyenkho = "";

            }
            finally
            {
                SqlHelper.CloseConnection();
            }
            return maphieuchuyenkho;
        }

        public List<PHIEU_CHUYEN_KHO> usp_SelectPHIEU_CHUYEN_KHO_MAX(string matau)
        {
            string spName = "select top 1 * from phieu_chuyen_kho where maphieuchuyenkho like '%" + matau + "%' order by maphieuchuyenkho desc";
            DataTable dt = new DataTable();
            List<PHIEU_CHUYEN_KHO> lst = new List<PHIEU_CHUYEN_KHO>();
            try
            {
                SqlHelper.OpenConnection();

                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, CommandType.Text);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, CommandType.Text);
                }

                lst = this.GetPHIEU_CHUYEN_KHO(dt);
            }
            catch 
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public List<PHIEU_CHUYEN_KHO> usp_SelectPHIEU_CHUYEN_KHO(string MaPhieuChuyenKho)
        {
            string spName = "usp_SelectPHIEU_CHUYEN_KHO";
            DataTable dt = new DataTable();
            List<PHIEU_CHUYEN_KHO> lst = new List<PHIEU_CHUYEN_KHO>();
            try
            {
                SqlHelper.OpenConnection();

                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@MaPhieuChuyenKho", MaPhieuChuyenKho);

                if (!SqlHelper.IsHaveTran())
                    dt = SqlHelper.ExecuteToDataTable(spName, par);
                else
                {
                    dt = SqlHelper.ExecuteToDataTable(SqlHelper.Tran, spName, par);
                }

                lst = this.GetPHIEU_CHUYEN_KHO(dt);
            }
            catch 
            {
            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }
            return lst;
        }

        public int usp_UpdatePHIEU_CHUYEN_KHO_Duyet(PHIEU_CHUYEN_KHO obj)
        {
            int iResult = 0;
            string spName = "usp_UpdatePHIEU_CHUYEN_KHO_Duyet";

            try
            {
                SqlParameter[] par = new SqlParameter[7];
                par[0] = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                par[0].Direction = ParameterDirection.ReturnValue;
                par[1] = new SqlParameter("@MaPhieuChuyenKho", obj.MaPhieuChuyenKho);
                par[2] = new SqlParameter("@NgayExport", obj.NgayExport);
                par[3] = new SqlParameter("@NguoiExport", obj.NguoiExport);
                par[4] = new SqlParameter("@NgayTao", obj.NgayXuat);
                par[5] = new SqlParameter("@NguoiTao", obj.NguoiXuat);
                par[6] = new SqlParameter("@GhiChu", obj.GhiChu);



                if (SqlHelper.IsHaveTran())
                    iResult = SqlHelper.ExecuteNonQuery(SqlHelper.Tran, spName, par);
                else
                    iResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, spName, par);

                iResult = int.Parse(par[0].Value.ToString());

            }
            catch 
            {
            }
            return iResult;
        }

        public List<PHIEU_CHUYEN_KHO> usp_SelectPHIEU_CHUYEN_KHOsSearch(PHIEU_CHUYEN_KHO obj, List<VAT_TU_PHU_TUNG> lstVTPT, int DaDuyet,string khonguon, string khodich)
        {
            string spName = "usp_SelectPHIEU_CHUYEN_KHOsSearch";
            DataTable dt = new DataTable();
            List<PHIEU_CHUYEN_KHO> lstpck = new List<PHIEU_CHUYEN_KHO>();
            try
            {
                string temp = "";
                for (int i = 0; i < lstVTPT.Count; i++)
                {
                    if (i == 0)
                        temp = "(";
                    temp += "'" + lstVTPT[i]._maVTPT + "'";
                    if (i < lstVTPT.Count - 1)
                        temp += ",";
                    else
                        temp += ")";
                }
                SqlHelper.OpenConnection();
                SqlParameter[] par = new SqlParameter[8];
                par[0] = new SqlParameter("@MaPhieuChuyenKho", obj.MaPhieuChuyenKho == "" ? null : obj.MaPhieuChuyenKho);
                if (obj.NgayXuat.Year < 2012)
                    par[1] = new SqlParameter("@NgayTaoFrom", null);
                else
                    par[1] = new SqlParameter("@NgayTaoFrom", obj.NgayXuat);
                if (obj.NgayXuatTo.Year < 2012)
                    par[2] = new SqlParameter("@NgayTaoTo", null);
                else
                    par[2] = new SqlParameter("@NgayTaoTo", obj.NgayXuatTo);
                par[3] = new SqlParameter("@NguoiTao", obj.NguoiXuat == "" ? null : obj.NguoiXuat);
                par[4] = new SqlParameter("@MaKhoNguon", khonguon == "" ? null : khonguon);
                par[5] = new SqlParameter("@MaKhoDich", khodich == "" ? null : khodich);
                par[6] = new SqlParameter("@MaVTPTs", temp == "" ? null : temp);
                if (DaDuyet == 0)// chua duyet
                    par[7] = new SqlParameter("@DaDuyet", false);
                else
                    if (DaDuyet == 1)// dc duyet
                        par[7] = new SqlParameter("@DaDuyet", true);
                    else
                        par[7] = new SqlParameter("@DaDuyet", null);

                dt = SqlHelper.ExecuteToDataTable(spName, par);
                lstpck = this.GetPHIEU_CHUYEN_KHO(dt);

            }
            catch 
            {

            }
            finally
            {
                if (!SqlHelper.IsHaveTran())
                    SqlHelper.CloseConnection();
            }


            return lstpck;
        }

        public void CsvExportPHIEU_CHUYEN_KHO(PHIEU_CHUYEN_KHO pXH, List<CHI_TIET_PCK> lst, string filepath)
        {
            if (lst.Count == 0)
                return;
            StringBuilder sb = new StringBuilder();

            string d = "0" + pXH.NgayXuat.Day.ToString();
            d = d.Substring(d.Length - 2, 2);
            string m = "0" + pXH.NgayXuat.Month.ToString();
            m = m.Substring(m.Length - 2, 2);
            string y = pXH.NgayXuat.Year.ToString();

            sb.Append(pXH.MaPhieuChuyenKho);
            sb.Append(";");         
            sb.Append(d + "." + m + "." + y);
            sb.Append(";");
            sb.Append(pXH.GhiChu);
            if (pXH.TinhTrang == 0)
            {
                for (int i = 0; i < lst.Count; i++)
                {
                    if (lst[i].TinhTrang == 0)
                    {
                        sb.AppendLine();
                        sb.Append(lst[i].MaThietBi);
                        sb.Append(";");
                        sb.Append(lst[i].SoLuong);
                        sb.Append(";");
                        sb.Append(lst[i].MaKhoNguonThamChieuSAP);
                        sb.Append(";");
                        sb.Append(lst[i].ValuationType.Trim());
                        sb.Append(";");
                        sb.Append(lst[i].MaKhoDichThamChieuSAP);
                        sb.Append(";");
                        sb.Append(lst[i].ValuationType.Trim());
                        sb.Append(";");
                        sb.Append(lst[i].MaChiTiet);
                    }
                }
            }

            File.WriteAllText(filepath, sb.ToString());
        }

        public void CsvExportPHIEU_CHUYEN_KHO_Cancelation(PHIEU_CHUYEN_KHO pXH, List<CHI_TIET_PCK> lst, string filepath)
        {
            if (lst.Count == 0)
                return;
            StringBuilder sb = new StringBuilder();

            string d = "0" + lst[0].NgayHuyChuyenKho.Value.Day.ToString();
            d = d.Substring(d.Length - 2, 2);
            string m = "0" + lst[0].NgayHuyChuyenKho.Value.Month.ToString();
            m = m.Substring(m.Length - 2, 2);
            string y = lst[0].NgayHuyChuyenKho.Value.Year.ToString();

            sb.Append(lst[0].MaPhieuHuyChuyenKho);
            sb.Append(";");
            sb.Append(pXH.MaPhieuChuyenKho);
            sb.Append(";");
            sb.Append(d + "." + m + "." + y);
            sb.Append(";");
            sb.Append(pXH.GhiChu);
            if (pXH.TinhTrang == 0)
            {
                for (int i = 0; i < lst.Count; i++)
                {
                    if (lst[i].TinhTrang == 0)
                    {
                        sb.AppendLine();

                        sb.Append(lst[i].MaChiTiet);
                        sb.Append(";");
                        sb.Append(lst[i].MaThietBi);
                    }
                }
            }

            File.WriteAllText(filepath, sb.ToString());
        }
    }
}
